package cn.main.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import text.excel.DataExcelExport;

import java.io.*;
import java.sql.Time;
import java.util.*;

/**
 * Author: chengwei
 * Date:   2016/9/8
 * Time:   17:07
 */
public class ExcelUtil {

    public static boolean exportExcel(DataExcelExport export, File file) {
        Workbook workbook = getWorkbook(file);

        if (workbook == null) return false;
        Sheet sheet = workbook.createSheet(file.getName().substring(0,file.getName().indexOf("."))+new Random().nextInt(100));
        DataFormat format = workbook.createDataFormat();
        //日期格式化
        CellStyle dateStyle = workbook.createCellStyle();
        dateStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
        //时间格式化
        CellStyle timeStyle = workbook.createCellStyle();
        timeStyle.setDataFormat(format.getFormat("HH:mm:ss"));

        Row row;
        int rowIndex = 0;

        //先创建表头
        row = sheet.createRow(rowIndex ++);
        Cell cell;
        int cellIndex = 0;
        for (Map.Entry<String,String> entry : export.getFieldName().entrySet()) {
            cell = row.createCell(cellIndex ++);
            cell.setCellValue(entry.getValue());
        }

        //添加数据
        for (Map<String,Object> map : export.getDataList()) {
            row = sheet.createRow(rowIndex ++);
            cellIndex = 0;
            for (String key : export.getFieldName().keySet()) {
                cell = row.createCell(cellIndex ++);
                Object v = map.get(key);
                if (v == null) {
                    cell.setCellValue("");
                } else if (v instanceof Time) {
                    cell.setCellStyle(timeStyle);
                    cell.setCellValue((Time)v);
                } else if (v instanceof Date) {
                    cell.setCellStyle(dateStyle);
                    cell.setCellValue((Date) v);
                } else if (DataUtils.toBoolean(v) || v instanceof Boolean || v instanceof Byte ) {
                    cell.setCellValue(DataUtils.toBoolean(v)?"是":"否");
                } else {
                    cell.setCellValue(DataUtils.toString(v));
                }
            }
        }

        BufferedOutputStream stream = null;
        try {
            stream = new BufferedOutputStream(new FileOutputStream(new File(file.getPath())));
            workbook.write(stream);
            return true;
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (stream != null) {
                    stream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static List<Map<String,Object>> importExcel(DataExcelExport export, File file) {
        Workbook workbook = getWorkbook(file);
        if (workbook == null) return null;
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        List<Map<String,Object>> list = new ArrayList<>();
        Map<String,Object> map;
        int cellIndex;
        Cell cell;
        Object value;
        //先获取表头对应的key
        Map<Integer,String> keys = new LinkedHashMap<>();
        Row row = sheet.getRow(0);
        int physicalNumber =row.getPhysicalNumberOfCells();
        for (Map.Entry<String,String> entry : export.getFieldName().entrySet()) {
            cellIndex = 0;
            while (cellIndex < physicalNumber) {
                cell = row.getCell(cellIndex);
//                map.put(,cell.getStringCellValue());
                if (cell.getStringCellValue().equals(entry.getValue())) {
                    keys.put(cellIndex,entry.getKey());
                    break;
                }
                cellIndex ++;
            }
        }
        for (int index = 1;index <= lastRowNum;index ++) {
            row = sheet.getRow(index);
//            int physicalNumber =row.getPhysicalNumberOfCells();
            map = new HashMap<>();
            cellIndex = 0;
            while (cellIndex < physicalNumber) {
                cell = row.getCell(cellIndex);
                if (cell == null) {
                    cellIndex ++;
                    continue;
                }

                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                    case Cell.CELL_TYPE_FORMULA:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            value = cell.getDateCellValue();
                        } else {
                            value = cell.getNumericCellValue();
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                        if ("是".equals(cell.getStringCellValue())) {
                            value = 1;
                        } else if ("否".equals(cell.getStringCellValue())){
                            value = 0;
                        } else {
                            value = cell.getStringCellValue();
                        }
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        value = null;
                        break;
                    default:
                        value = cell.getStringCellValue();
                }
                map.put(keys.get(cellIndex),value);
                cellIndex ++;
            }
            list.add(map);
        }

        return list;
    }

    private static Workbook getWorkbook (File file) {
        Workbook workbook = null;
        try {
            if (file.getName().toLowerCase().endsWith(".xls")) {
                if (!file.exists()) {
                    workbook = new HSSFWorkbook();

                } else {
                    workbook = new HSSFWorkbook(new FileInputStream(file.getPath()));
                }
            } else if (file.getName().toLowerCase().endsWith(".xlsx")) {
                if (!file.exists()) {
                    workbook = new XSSFWorkbook();

                } else {
                    workbook = new XSSFWorkbook(new FileInputStream(file.getPath()));
                }
            }
            return workbook;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
    }
}
